Introduction to M Language, Functions, and Expressions in Power BI

Course: Microsoft Power BI Training

Topic: Introduction to Power Query M Language, Functions, and Expressions

Course Link: Microsoft Power BI Training at JustAcademy

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to M Language in Power BI

Power Query M is the formula language used by Power Query to connect to data sources, extract data, clean information, transform tables, and prepare data for reporting. In Power BI Desktop, M is primarily used inside Power Query Editor during the data preparation and transformation process.

When a user removes columns, filters rows, changes data types, combines tables, or creates custom columns, Power Query usually generates corresponding M expressions automatically. Users can also write or modify these expressions manually for more advanced transformations.

M is particularly useful when data needs to be cleaned consistently before it is loaded into the Power BI data model. The transformation steps can be executed again when the data is refreshed, subject to the source and refresh configuration.

1.1 What Is M Language?

M is a functional, case-sensitive language designed for data transformation and data combination. It allows users to describe how data should be retrieved and transformed through expressions, functions, operators, and step definitions.

For example, the expression below adds two numbers:

10 + 20

The result is 30. This is a simple M expression because it combines values using an operator and evaluates to a result.

A more practical example creates a new column in a table:

Table.AddColumn(
    Source,
    "Total Sales",
    each [Quantity] * [Unit Price],
    type number
)

This expression adds a column named Total Sales and calculates its value by multiplying Quantity by Unit Price for each row. The identifier Source must refer to an existing table value in the query.

1.2 Why Is M Language Important?

• It automates repetitive data-cleaning tasks.
• It allows users to create advanced transformation rules.
• It supports calculations and text manipulation during data preparation.
• It helps combine, filter, sort, and restructure tables.
• It makes transformations repeatable during data refreshes.
• It allows users to inspect and customize the steps generated by Power Query.
• It supports reusable custom functions for recurring transformation requirements.

1.3 Learning Objectives

• Understand the purpose of Power Query M in Power BI.
• Distinguish between expressions, values, functions, and variables.
• Understand the structure of a Power Query query.
• Learn the use of let and in expressions.
• Use operators and built-in functions.
• Create custom columns with M formulas.
• Understand function parameters and return values.
• Handle null values and common errors.
• Read and modify M code using the formula bar and Advanced Editor.
• Build a practical data transformation using multiple M expressions.

2. Understanding Power Query and M Language

2.1 What Is Power Query?

Power Query is a data preparation tool available in Power BI Desktop and other Microsoft products. It provides a user interface for connecting to data sources and applying transformations before loading the resulting data.

For example, a sales analyst might receive a spreadsheet containing inconsistent product names, missing values, incorrect data types, and unnecessary columns. Power Query can be used to clean the dataset and create a reliable table for reporting.

2.2 Relationship Between Power Query and M

Power Query provides the graphical interface, while M defines the transformation logic behind the query. Many common actions performed through the interface generate M code automatically.

2.3 M Language vs DAX

M and DAX are both important in Power BI, but they solve different problems. M is primarily used to prepare data before it enters the model. DAX is used for calculations and analytical logic within the data model.

Important: M is not a replacement for DAX. Use the language that best matches the task. Clean and reshape data in Power Query when appropriate, and use DAX for model-based calculations and interactive analysis.

3. Understanding Values and Data Types in M

3.1 What Is a Value?

A value is a piece of data produced by evaluating an expression. M supports primitive values, such as numbers and text, and structured values, such as lists, records, and tables.

3.2 Expressions and Results

An expression describes how a value should be produced. When M evaluates an expression, it returns the corresponding result unless an error occurs.

5 + 3

Result: 8.

"Power" & " Query"

Result: Power Query.

if 100 > 50 then "High" else "Low"

Result: High.

3.3 Why Data Types Matter

Data types affect which operations are valid and how values are interpreted. Adding two numbers is valid, but attempting to multiply incompatible text and numeric values may cause an error.

• Use numeric types for quantities and amounts.
• Use text for names, descriptions, and category labels.
• Use date or date/time types for temporal values.
• Use logical values for true/false conditions.
• Use null to represent missing or unknown information when appropriate.

4. Understanding Expressions in M Language

4.1 What Is an Expression?

An expression is a formula that M evaluates to produce a value. Expressions can contain literals, variables, operators, function calls, conditions, and other expressions.

4.2 Arithmetic Expressions

10 + 5
10 - 5
10 * 5
10 / 5
10 mod 3

4.3 Text Expressions

The ampersand operator & combines text values.

"Microsoft" & " " & "Power BI"

Result: Microsoft Power BI.

To combine values from columns, use a custom column expression such as:

[First Name] & " " & [Last Name]

Before combining text columns, consider whether either column may contain null values or unexpected data types.

4.4 Comparison Expressions

Comparison operators are used to test whether values are equal, unequal, greater than, or less than other values.

4.5 Logical Expressions

Logical operators combine or reverse conditions.

[Sales Amount] >= 50000 and [Order Status] = "Completed"

This expression returns true when both conditions are true.

[Region] = "West" or [Region] = "North"

This expression returns true when at least one condition is true.

not ([Order Status] = "Cancelled")

This expression reverses the result of the condition inside the parentheses.

5. Understanding the let and in Expressions

5.1 What Is a let Expression?

The let expression defines named values that can be referenced later in the same expression. It is especially useful for organizing a query into understandable steps and intermediate calculations.

The in keyword identifies the final expression whose result is returned.

5.2 Basic Syntax

let
    Variable1 = Expression1,
    Variable2 = Expression2,
    Variable3 = Expression3
in
    FinalExpression

Each variable is assigned an expression. Variables are separated by commas, and the final result is specified after in.

5.3 Simple Example

let
    Price = 100,
    Quantity = 5,
    Total = Price * Quantity
in
    Total

Result: 500.

The expression assigns 100 to Price and 5 to Quantity, calculates Total, and returns Total as the final result.

5.4 Using let to Improve Readability

Suppose a business calculates sales after a discount and then adds tax.

let
    GrossAmount = 1000,
    DiscountRate = 0.10,
    TaxRate = 0.05,
    DiscountedAmount = GrossAmount * (1 - DiscountRate),
    FinalAmount = DiscountedAmount * (1 + TaxRate)
in
    FinalAmount

Result: 945.

Using named intermediate values makes the calculation easier to read and maintain than writing the entire expression as one long formula.

5.5 Understanding Query Steps

A typical Power Query query uses a let expression to define transformation steps and an in expression to return the final table.

let
    Source = Excel.Workbook(
        File.Contents("C:\Data\Sales.xlsx"),
        null,
        true
    ),
    SalesSheet = Source{[Item="Sales", Kind="Sheet"]}[Data],
    PromotedHeaders = Table.PromoteHeaders(
        SalesSheet,
        [PromoteAllScalars=true]
    ),
    ChangedTypes = Table.TransformColumnTypes(
        PromotedHeaders,
        {{"Sales Amount", type number}}
    )
in
    ChangedTypes

This is an illustrative example. The file path, workbook structure, worksheet name, and column names must match the actual source. The query loads a workbook, selects a worksheet, promotes the first row to headers, and changes the Sales Amount data type.

5.6 Understanding the Applied Steps Pane

The Applied Steps pane shows the sequence of transformations in a query. Selecting a step displays the table state at that point in the process and, when available, its corresponding formula.

• Each step refers to the output of a previous step or another expression.
• Later steps can use columns created by earlier steps.
• Removing or renaming a step may affect expressions that refer to it.
• Clear step names help other users understand the query.

6. Functions in M Language

6.1 What Is a Function?

A function is a reusable expression that accepts input values, called parameters, and produces an output value. M includes a large library of built-in functions for working with numbers, text, dates, tables, lists, records, types, and errors.

For example, Text.Upper converts text to uppercase, while Date.Year extracts the year from a date value.

6.2 Function Syntax

FunctionName(argument1, argument2)

A function name is followed by parentheses containing the required arguments. Some functions accept optional arguments, while others require specific input types.

6.3 Example of a Built-in Function

Text.Upper("power bi")

Result: POWER BI.

Number.Round(123.456, 2)

Result: 123.46.

Date.Year(#date(2026, 10, 21))

Result: 2026.

6.4 Function Parameters and Return Values

A parameter is a named input used inside a function definition. An argument is the actual value supplied when the function is called. The return value is the result produced by evaluating the function.

In this example:

Text.Upper("sales")

• Function: Text.Upper.
• Argument: "sales".
• Return value: "SALES".

7. Commonly Used M Functions

7.1 Text Functions

7.2 Number Functions

7.3 Date and Time Functions

7.4 Logical and Conditional Expressions

M uses the if ... then ... else expression to evaluate conditions.

if [Sales Amount] >= 50000 then "High"
else if [Sales Amount] >= 20000 then "Medium"
else "Low"

This expression classifies a sales amount as High, Medium, or Low. The most restrictive threshold is evaluated first so that high values are not assigned to a lower category.

7.5 Table Functions

8. Creating Custom Functions in M

8.1 What Is a Custom Function?

A custom function is a user-defined function that encapsulates transformation logic so it can be reused. Instead of rewriting the same formula repeatedly, users can define the logic once and call the function with different inputs.

8.2 Basic Custom Function

(x) => x * 2

This function accepts one parameter, x, and returns x multiplied by 2. Calling the function with 5 produces 10.

let
    DoubleValue = (x) => x * 2,
    Result = DoubleValue(5)
in
    Result

Result: 10.

8.3 Custom Function with Two Parameters

(Price, Quantity) => Price * Quantity

This function calculates a total using the supplied price and quantity.

let
    CalculateTotal = (Price, Quantity) => Price * Quantity,
    Result = CalculateTotal(250, 4)
in
    Result

Result: 1000.

8.4 Custom Function for Discount Calculation

(Amount as number, DiscountRate as number) as number =>
    Amount * (1 - DiscountRate)

This function takes an amount and a decimal discount rate. For example, calling it with 1000 and 0.10 returns 900. The type annotations communicate the intended input and output types.

The discount rate should be supplied as a decimal, such as 0.10 for 10%. If the source stores the discount as the number 10, convert it appropriately before using this formula.

8.5 Why Custom Functions Are Useful

• They reduce duplicated transformation logic.
• They make complex rules easier to maintain.
• They can be reused across queries.
• They support consistent processing of multiple files or tables.
• They help organize advanced Power Query solutions into smaller components.

9. Working with Lists, Records, and Tables

9.1 Lists

A list is an ordered collection of values enclosed in curly braces.

{10, 20, 30, 40}

A list can contain text, numbers, dates, records, or other values. List functions can be used to count, filter, transform, or combine its elements.

Example:

List.Sum({10, 20, 30})

Result: 60.

9.2 Records

A record stores named fields and their corresponding values. Records are written using square brackets.

[Product = "Laptop", Price = 40000, Quantity = 2]

Record fields can be accessed by their names:

[Product = "Laptop", Price = 40000][Price]

Result: 40000.

9.3 Tables

A table is a structured collection of rows and columns. Power Query transformations generally operate on tables and return a transformed table.

#table(
    {"Product", "Quantity"},
    {
        {"Laptop", 2},
        {"Mouse", 5},
        {"Monitor", 3}
    }
)

This expression creates a small table with Product and Quantity columns. It can be used for learning and testing M expressions without connecting to an external source.

9.4 Filtering a Table with M

Table.SelectRows(
    Source,
    each [Sales Amount] >= 20000
)

This expression returns rows from Source where Sales Amount is at least 20,000. Source must refer to an existing table whose Sales Amount field is available and compatible with the comparison.

9.5 Adding a Calculated Column to a Table

Table.AddColumn(
    Source,
    "Gross Amount",
    each [Quantity] * [Unit Price],
    type number
)

This expression creates a Gross Amount column using the row's Quantity and Unit Price values. The expression assumes both columns exist and contain compatible numeric values.

10. Understanding M Language Operators

Operators perform actions on values, such as arithmetic, comparison, and logical evaluation.

10.1 Operator Precedence

When an expression contains multiple operators, M follows defined evaluation rules and operator precedence. Parentheses help make the intended calculation clear.

10 + 5 * 2

Result: 20, because multiplication is evaluated before addition.

(10 + 5) * 2

Result: 30, because the expression inside the parentheses is evaluated first.

11. Handling Null Values and Errors

11.1 Understanding Null

The value null represents missing, absent, or unknown information. It is different from zero, an empty text string, or the logical value false.

11.2 Replacing Null Values in an Expression

The null-coalescing operator can provide an alternative when a value is null.

[Quantity] ?? 0

This expression returns Quantity when it is not null; otherwise, it returns zero. Only use zero as a replacement when it accurately represents the intended business meaning.

11.3 Handling Null Values in a Calculation

if [Quantity] = null then null
else [Quantity] * [Unit Price]

This example preserves a missing quantity as null instead of assuming that the quantity is zero.

11.4 Understanding Errors

An M error occurs when an expression cannot be evaluated successfully. Common causes include incompatible data types, invalid conversions, division by zero, missing fields, and incorrect function arguments.

11.5 Using try and otherwise

The try expression can capture an error and provide an alternative result.

try Number.From([Sales Amount]) otherwise null

This attempts to convert Sales Amount to a number. If the conversion fails, the expression returns null.

Replacing an error with null should be a deliberate decision. For critical data, it may be better to identify and investigate the invalid records rather than silently hiding the problem.

11.6 Diagnosing Errors

1. Identify the step that first produces the error.
2. Inspect the values in the affected column.
3. Check whether the column's data type matches the operation.
4. Verify the function name, arguments, and referenced columns.
5. Use a controlled error-handling expression where appropriate.
6. Validate the output after the correction.

12. Creating M Expressions in Power Query Editor

12.1 Open Power Query Editor

1. Open Power BI Desktop.
2. Load a dataset or open an existing Power BI report.
3. Select Home > Transform Data.
4. Choose the query you want to modify.
5. Review the existing columns and Applied Steps.

12.2 Use the Custom Column Dialog

1. Select the Add Column tab.
2. Select Custom Column.
3. Enter a meaningful name for the new column.
4. Write the M expression in the formula box.
5. Check the formula and the referenced columns.
6. Select OK to create the column.
7. Review the resulting values and set the correct data type.

For example, enter the following expression to calculate gross sales:

[Quantity] * [Unit Price]

12.3 Use the Formula Bar

The formula bar displays the M expression for the selected query step when it is available. It is useful for inspecting and making small changes to a transformation without replacing the entire query.

12.4 Use Advanced Editor

Advanced Editor displays the complete M script for a query. It is useful when building multiple steps, editing data-source logic, or organizing complex transformations.

To use it:

1. Open Power Query Editor.
2. Select the query you want to edit.
3. Choose Home > Advanced Editor.
4. Review the existing let and in structure.
5. Make the required change while preserving valid step references and syntax.
6. Select Done and review the results.

13. Complete Practical Example: Sales Data Transformation

13.1 Project Objective

Use M language to create a sample sales table, calculate sales values, classify transactions, filter the records, and produce a final transformed table.

13.2 Sample Source Data

13.3 Complete M Code

For practice, open Power Query Editor, create a Blank Query, open Advanced Editor, and replace the existing expression with the following code. This example creates its own sample data, so it does not require an external file.

let
    Source = #table(
        type table [
            OrderID = Int64.Type,
            Product = text,
            Quantity = Int64.Type,
            UnitPrice = number,
            Region = text
        ],
        {
            {101, "Laptop", 2, 40000, "West"},
            {102, "Mouse", 5, 500, "North"},
            {103, "Monitor", 3, 12000, "West"},
            {104, "Keyboard", 4, 1500, "South"},
            {105, "Printer", 2, 26000, "North"}
        }
    ),
    AddedSales = Table.AddColumn(
        Source,
        "Sales Amount",
        each [Quantity] * [UnitPrice],
        type number
    ),
    AddedCategory = Table.AddColumn(
        AddedSales,
        "Sales Category",
        each
            if [Sales Amount] >= 50000 then "High"
            else if [Sales Amount] >= 20000 then "Medium"
            else "Low",
        type text
    ),
    FilteredRows = Table.SelectRows(
        AddedCategory,
        each [Sales Amount] >= 20000
    ),
    SortedRows = Table.Sort(
        FilteredRows,
        {{"Sales Amount", Order.Descending}}
    )
in
    SortedRows

13.4 Explanation of the Code

13.5 Expected Output

The Mouse and Keyboard rows are excluded because their calculated sales amounts are below 20,000. The remaining rows are sorted from highest to lowest Sales Amount.

13.6 Project Extensions

• Add a Discount Rate column and calculate Net Sales.
• Create a custom function that calculates discounted sales.
• Add a column containing the year or month of each order using an Order Date field.
• Group the data by Region and calculate total sales.
• Add error handling for invalid quantities or prices.
• Replace the sample table with data imported from an Excel or CSV file.

14. Best Practices for M Language

• Use descriptive step names: Names such as FilteredRows and AddedSales make the query easier to understand.
• Keep expressions readable: Use indentation and intermediate variables for complex logic.
• Validate data types: Check the type of each column before performing calculations.
• Handle null values carefully: Do not replace missing values automatically without considering their meaning.
• Test formulas on sample records: Confirm that the results are correct before applying the logic to the full dataset.
• Use built-in functions where possible: Built-in functions are easier to understand and maintain than unnecessarily complicated custom expressions.
• Reuse custom functions: Encapsulate transformation logic when it must be applied consistently in multiple places.
• Review Applied Steps: Ensure each step uses the expected input and that later steps reference existing columns.
• Be careful with source paths: Hard-coded file paths may not work on another computer or after files are moved.
• Consider query folding: For supported sources, some transformations can be translated into source-side operations. Avoid assuming every M expression folds; check folding indicators and source behavior where performance matters.
• Keep a backup before major changes: Save a copy or version of important queries before replacing large sections of M code.
• Choose M and DAX appropriately: Use M for preparation and DAX for model-based analytical calculations when suitable.

15. Common Errors and Troubleshooting

16. Interview Questions and Answers

Q1. What is M language in Power BI?

M is the formula language used by Power Query to connect to data sources and define data transformation steps before loading data into the model.

Q2. What is the difference between M and DAX?

M is primarily used for data extraction and preparation in Power Query. DAX is used for calculations in the Power BI data model, including measures and calculated columns.

Q3. What is an expression in M?

An expression is a formula that M evaluates to produce a value. Examples include arithmetic calculations, function calls, conditional expressions, and let expressions.

Q4. What is a function in M?

A function is a reusable operation that accepts input arguments and returns a result. M includes built-in functions and allows users to define custom functions.

Q5. What is the purpose of let and in?

The let keyword defines named values or intermediate steps, while in identifies the final expression whose result is returned.

Q6. What is the difference between a list, record, and table?

A list is an ordered collection of values, a record contains named fields, and a table contains rows and columns.

Q7. What does the each keyword mean?

The each keyword is shorthand for a function applied to the current row or item in many Power Query operations. It is commonly used in table transformations such as adding a column or filtering rows.

Q8. How do you add a custom column using M?

Use Add Column > Custom Column and enter a formula such as [Quantity] * [Unit Price]. Power Query generates the corresponding table transformation step.

Q9. What is a custom function?

A custom function is a user-defined function that packages reusable transformation logic. It can accept parameters and be called with different inputs.

Q10. How does M handle errors?

M can raise errors when expressions cannot be evaluated. The try expression can capture an error and optionally provide an alternative result through otherwise or a catch handler.

Q11. What is null in M language?

Null represents an absent, missing, or unknown value. It is not the same as zero, an empty string, or false.

Q12. What is the purpose of Table.SelectRows?

Table.SelectRows filters a table using a condition and returns only the rows that satisfy that condition.

Q13. What is the purpose of Table.AddColumn?

Table.AddColumn creates a new column by evaluating a specified function for each row of a table.

Q14. What is query folding?

Query folding occurs when Power Query translates supported transformations into operations that a source system can execute. It can improve performance by moving work to the source, but not every transformation or source supports folding.

Q15. Where can you inspect M code in Power BI?

You can inspect an individual step in the formula bar or view the complete query in Advanced Editor in Power Query Editor.

17. Practice Exercises

Exercise 1: Basic Expressions

Write M expressions to perform the following operations:

• Add 150 and 250.
• Multiply 25 by 8.
• Combine the text values "Power" and "BI".
• Round 89.678 to two decimal places.
• Extract the year from a date value.

Exercise 2: Conditional Logic

Create a conditional expression that returns:

• Excellent when the score is 90 or above.
• Good when the score is 70 or above but below 90.
• Average when the score is 40 or above but below 70.
• Needs Improvement when the score is below 40.

Exercise 3: Custom Function

Write a function that accepts an amount and a tax rate, then calculates the total amount after tax. Test it using an amount of 2,000 and a tax rate of 0.05.

Exercise 4: Table Transformation

Create a table with Product, Quantity, and Unit Price columns. Use Table.AddColumn to calculate Total Amount and Table.SelectRows to keep rows with a Total Amount greater than 5,000.

Exercise 5: Error Handling

Write a conversion expression that attempts to convert a text field to a number and returns null when the conversion fails. Consider how the report should identify records with invalid data rather than silently losing that information.

18. Quick Revision Checklist

• Know what M language is and why Power Query uses it.
• Understand the difference between M and DAX.
• Distinguish expressions from the values they produce.
• Recognize numbers, text, logical values, null, lists, records, and tables.
• Understand arithmetic, text, comparison, and logical operators.
• Use let and in to organize query steps and intermediate values.
• Understand function names, arguments, parameters, and return values.
• Recognize common text, number, date, and table functions.
• Create custom functions for reusable transformations.
• Handle errors and missing values according to business requirements.
• Inspect M code in the formula bar and Advanced Editor.
• Validate transformation results and review query performance.

19. Conclusion

M language is a fundamental part of Power BI data preparation. It enables analysts to express transformations as reusable, repeatable logic and supports everything from simple arithmetic to advanced table manipulation.

By understanding values, expressions, functions, operators, let and in structures, and error handling, users can move beyond basic interface operations and build more flexible Power Query solutions. Practice with small datasets first, verify each transformation, and use the built-in M function reference when working with unfamiliar functions.

20. Additional Learning Resources

• Microsoft Learn: Power Query M Formula Language Introduction
• Microsoft Learn: M Language Basic Concepts
• Microsoft Learn: Let Expressions
• Microsoft Learn: M Language Operators
• Microsoft Learn: Power Query M Function Reference
• Microsoft Learn: M Language Error Handling
• Microsoft Power BI Training at JustAcademy
• Register for a JustAcademy Course Demo
• Visit the JustAcademy Website